import os
import pandas as pd
from datetime import datetime
from openpyxl import Workbook
from openpyxl.styles import Alignment, Font, PatternFill, Border, Side


def process_meter_files(input_dir="input", output_dir="output", save_func=None):
    """通过水表表号录入：模板 + 可选远程表 + 可选电脑表（保留水表号前导零）"""
    os.makedirs(output_dir, exist_ok=True)

    excel_files = [f for f in os.listdir(input_dir) if f.endswith((".xlsx", ".xls"))]
    if len(excel_files) < 1:
        raise ValueError("请至少上传一个模板文件！")

    # 自动识别文件类型
    template_file = None
    remote_file = None
    computer_files = []

    for f in excel_files:
        path = os.path.join(input_dir, f)
        try:
            df = pd.read_excel(path, nrows=3)
        except Exception:
            continue

        cols = df.columns.astype(str).tolist()
        if all(c in cols for c in ["*水表表号", "*水表止度", "*抄表日期", "*用水状态"]):
            template_file = f
        elif "表地址" in cols and "抄表数据" in cols:
            remote_file = f
        elif "表号" in cols and "总电能" in cols:
            computer_files.append(f)

    if not template_file:
        raise ValueError(
            "未检测到模板文件，请确认模板文件包含：*水表表号、*水表止度、*抄表日期、*用水状态"
        )

    if not remote_file and not computer_files:
        raise ValueError("至少需要上传远程表或电脑表其中之一！")

    merged_df = pd.DataFrame(
        columns=["*水表表号", "*水表止度", "*抄表日期", "*用水状态"]
    )
    today = datetime.now().strftime("%Y-%m-%d")

    # ---------- 远程表 ----------
    if remote_file:
        try:
            remote_df = pd.read_excel(os.path.join(input_dir, remote_file), dtype=str)
            tmp = pd.DataFrame(
                {
                    "*水表表号": remote_df["表地址"].astype(str).str.strip(),
                    "*水表止度": remote_df["抄表数据"],
                    "*抄表日期": today,
                    "*用水状态": "正常",
                }
            )
            merged_df = pd.concat([merged_df, tmp], ignore_index=True)
        except Exception as e:
            raise ValueError(f"远程表处理失败：{e}")

    # ---------- 电脑表 ----------
    for file in computer_files:
        try:
            df = pd.read_excel(os.path.join(input_dir, file), header=None, dtype=str)
            df.columns = df.iloc[0]
            df = df.drop([0, 1]).reset_index(drop=True)
            tmp = pd.DataFrame(
                {
                    "*水表表号": df["表号"].astype(str).str.strip(),
                    "*水表止度": df["总电能"],
                    "*抄表日期": today,
                    "*用水状态": "正常",
                }
            )
            merged_df = pd.concat([merged_df, tmp], ignore_index=True)
        except Exception as e:
            raise ValueError(f"电脑表 {file} 处理失败：{e}")

    if merged_df.empty:
        raise ValueError("未检测到可导入的数据，请检查远程表或电脑表格式。")

    # ---------- 保留前导零 ----------
    # 不再转为 int，而是字符串保留原样
    merged_df["*水表表号"] = merged_df["*水表表号"].astype(str).str.strip()
    merged_df["*水表止度"] = (
        pd.to_numeric(merged_df["*水表止度"], errors="coerce").fillna(0).astype(int)
    )

    # ---------- 样式化保存 ----------
    wb = Workbook()
    ws = wb.active
    ws.title = "抄表录入"
    ws.append(["*水表表号", "*水表止度", "*抄表日期", "*用水状态"])

    header_fill = PatternFill("solid", fgColor="4F81BD")
    header_font = Font(bold=True, color="FFFFFF")
    center_align = Alignment(horizontal="center", vertical="center")
    thin = Side(style="thin")
    border = Border(left=thin, right=thin, top=thin, bottom=thin)

    # 表头样式
    for cell in ws[1]:
        cell.fill = header_fill
        cell.font = header_font
        cell.alignment = center_align
        cell.border = border
    ws.row_dimensions[1].height = 25

    # 写入数据
    for row in merged_df.itertuples(index=False, name=None):
        ws.append(row)

    # 设置样式
    for row in range(2, len(merged_df) + 2):
        for col in range(1, 5):
            cell = ws.cell(row=row, column=col)
            cell.alignment = center_align
            cell.border = border
            if col == 1:
                # 保留前导零的文本格式
                cell.number_format = "@"
            elif col == 2:
                cell.number_format = "0"

    # 调整列宽
    for col in ws.columns:
        max_len = max(len(str(c.value or "")) for c in col)
        ws.column_dimensions[col[0].column_letter].width = max(12, max_len + 2)

    prev_month = (datetime.now().month - 1) or 12
    prev_year = (
        datetime.now().year if datetime.now().month > 1 else datetime.now().year - 1
    )
    out_name = f"通过水表表号录入-{prev_year}年{prev_month:02d}月成星抄表录入.xlsx"
    output_path = os.path.join(output_dir, out_name)

    if save_func:
        save_func(wb, output_path)
    else:
        wb.save(output_path)

    return output_path
